<?php
class model_visitafter extends framework_base_model {
	/**
	 * @desc 添加走访信息
	 * @param array 
	 * @return number
	 */
	public static function visitAdd($data) {
    	if(!is_array($data)) {
    		return 0;
    	}
		$sql1 = "insert into `weixin_customer_info` set ";
		$sql2 = "insert into `weixin_question_info` set ";
		$sql3 = "insert into `weixin_customer_question` set ";
		foreach ($data as $k => $v) {
			foreach($v as $key=>$value){
				if($k == 'a'){
					$sql1 .= "{$key} = '{$value}', ";
				}elseif($k == 'b'){
					$sql2 .= "{$key} = '{$value}', ";
				}else{
					foreach($value as $k2 => $v2){
						$sql3_[$k2] .= "`{$key}` = '{$v2}', ";
					}
				}
			}
		}
		$sql1 .= '`raw_add_time` = NOW()';
		$sql2 .= '`raw_add_time` = NOW()';
		$sql3 .= '`raw_add_time` = NOW(),';
    	try{
			$pdo = self::DB() -> dbh;
			$pdo->beginTransaction();
			$stmt= $pdo -> prepare($sql1);
    		$rel = $stmt->execute();
			$last_id = $pdo->lastInsertId();
			$sql_last_id .= ", `customer_info_id` = '{$last_id}'";

			$stmt= $pdo -> prepare($sql2.$sql_last_id);
    		$rel = $stmt->execute();

			foreach($sql3_ as $v){
				$stmt= $pdo -> prepare($sql3.$v.trim($sql_last_id, ','));
    			$rel = $stmt->execute();
			}
			$pdo->commit();
			return true;
    	}catch(Exception $e){
			$pdo->rollBack();
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    		return 0;
    	}
    }
    /*
     * 根据ID获取访问信息
     */
    public static function getVisitInfoById($id) {
    	$info = array();
    	if(intval($id) <= 0) {
    		return $info;
    	}
		$filds = "a.`customer_name`,a.`province`,a.`city`,a.`area`,a.`street`,a.`general`,a.`ability`,a.`status`,a.`part`,a.`sale_name`,a.`author`,a.`editer`,a.`raw_update_time`,b.`collection_name`,b.`collection_part`,b.`collection_tel`,b.`collection_study`,b.`collection_channel`,b.`collection_date`,b.`agreement`,b.`product`,b.`grade`,b.`format`,b.`number`,b.`betch`,b.`occur_date`,b.`person_name`,b.`person_study`,b.`person_tel`";
    	$sql1 = "select {$filds} from `weixin_customer_info` as a,`weixin_question_info` as b where a.`customer_info_id` = b.`customer_info_id` and a.`customer_info_id` = :customer_info_id and a.`delete_flag` = 0";
		$sql2 = "select `question`,`question_desc` from `weixin_customer_question` where `customer_info_id` = :customer_info_id and `delete_flag` = 0;";
		$sql3 = "select c.`small_class`,c.`heigh_class`,c.`nature`,c.`main_unit`,c.`relation_unit`,c.`correct_date`,c.`repeat_num`,c.`correction_batch`,c.`correction_date`,c.`solution_unit`,c.`solution_main`,c.`solution_name`,c.`solution_tel`,c.`determi_unit`,c.`question_resean`,c.`is_solution`,c.`solution_measure`,c.`solution_date`,c.`solution_result`,c.`check_num`,c.`solution_status`,c.`repeat_content`,c.`repeat_date`,c.`repeat_name` from `weixin_question_solution` c where c.`customer_info_id` = :customer_info_id and c.`delete_flag` = 0";
    	try {
			$pdo = self::DB() -> dbh;
    		$st = $pdo -> prepare($sql1);
    		$st -> execute(array(":customer_info_id" => intval($id)));
    		$info['info'] = $st -> fetch();

			$st = $pdo -> prepare($sql2);
    		$st -> execute(array(":customer_info_id" => intval($id)));
    		$info['question'] = $st -> fetchAll();

			$st = $pdo -> prepare($sql3);
    		$st -> execute(array(":customer_info_id" => intval($id)));
    		$info['solution'] = $st -> fetch();
    	} catch (Exception $e){
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    	}
    	return $info;
    }
    /**
     * @desc 更新走访记录
     * @param unknown $id
     * @param unknown $data
     * @return unknown|number
     */
    public static function updateVisitInfo($id,$data) {
    	try{
			$ids = implode(',',$id);
			$sql1 = "select `customer_question_id` from `weixin_customer_question` where customer_info_id=$ids";
    		$sql2 = 'UPDATE `weixin_customer_info` as a,`weixin_question_info` as b SET ';
			$sql3 = "update `weixin_customer_question` as c set ";
			$sql4 = "insert into `weixin_customer_question` set ";
			$sql5 = "update `weixin_customer_question` set `delete_flag` = 1 where customer_info_id=$ids";
    		$binds = array();
			foreach ($data as $k => $v) {
				foreach($v as $key=>$value){
					if($k == 'a'){
						$sql2 .= "a.{$key} = '{$value}', ";
					}elseif($k == 'b'){
						$sql2 .= "b.{$key} = '{$value}', ";
					}else{
						foreach($value as $k2 => $v2){
							$sql3_[$k2] .= "c.`{$key}` = '{$v2}', ";
						}
					}
				}
			}
    		$sql2 = trim($sql2,', ');
    		$sql2 .= " WHERE a.`customer_info_id` = b.`customer_info_id` and a.`customer_info_id` = {$ids}";
			$pdo = self::DB() -> dbh;
			$pdo -> beginTransaction();

			$stm = $pdo -> prepare($sql1);
			$stm -> execute();
			$question_id = $stm -> fetchAll();
			
    		$stm = $pdo -> prepare($sql2);
    		$rel = $stm->execute($binds);
			
			$stm = $pdo -> prepare($sql5);
    		$rel = $stm->execute($binds);

			if(!empty($sql3_))
			foreach($sql3_ as $k => $v){
				$v = trim($v, ", ");
				if(!empty($question_id[$k]['customer_question_id'])){
					$where = ",c.`delete_flag`=0 where c.`customer_question_id` = {$question_id[$k]['customer_question_id']}";
					$stm = $pdo -> prepare($sql3.$v.$where);
					$stm->execute($binds);
				}else{
					$where = ", customer_info_id=$ids";
					$v = str_replace("c.","", $v);
					$stm = $pdo -> prepare($sql4.$v.$where);
					$stm->execute($binds);
				}
				
			}			

			$pdo -> commit();
    		return true;
    	}catch(Exception $e){
			$pdo -> rollBack();
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    		return 0;
    	}
    }
    /**
     * 获取走访列表
     * @param number $nextNum
     * @param number $pagesize
     * @param unknown $condition
     * @return unknown|Ambigous <number, unknown>
     */
    public static function visitList($nextNum = 0,$pagesize = 0,$condition = array()) {
    	$filds = "a.`customer_info_id`,a.`customer_name`,a.`province`,a.`city`,a.`area`,a.`street`,a.`general`,a.`ability`,a.`status`,a.`part`,a.`sale_name`,a.`author`,a.`editer`,a.`raw_add_time`,a.`raw_update_time`,b.`collection_name`,b.`collection_part`,b.`collection_tel`,b.`collection_study`,b.`collection_channel`,b.`collection_date`,b.`agreement`,b.`product`,b.`grade`,b.`format`,b.`number`,b.`betch`,b.`occur_date`,b.`person_name`,b.`person_study`,b.`person_tel`";
    	$sql = "select {$filds} from `weixin_customer_info` as a,`weixin_question_info` as b where a.`customer_info_id` = b.`customer_info_id` and a.`delete_flag` = 0";
		$where = "";
    	if(is_array($condition)&& count($condition)>0){
    		if(isset($condition['customer_name'])) {
    			$where .= " and a.`customer_name` like '%{$condition['customer_name']}%'";
    		}
			if(isset($condition['sale_name'])) {
    			$where .= " and a.`sale_name` like '%{$condition['sale_name']}%'";
    		}
    	}
    	$where .= " order by a.`raw_add_time` desc";
    	$limit = "";
        if($pagesize != 0 ){
    		$limit = " limit {$nextNum},{$pagesize}";
    	}
    	$result = array();
    	try {
    		$st = self::DB()->dbh->prepare($sql.$where.$limit);
    		$st->execute();
    		$result = $st->fetchAll();
    	}catch(Exception $e){
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    		$result = array();
    	}
		
		$sql = "select count(*) from `weixin_customer_info` as a,`weixin_question_info` as b where a.`customer_info_id` = b.`customer_info_id` and a.`delete_flag` = 0 {$where}";
		$binds2 = array();
    	try {
    		$st = self::DB()->dbh->prepare($sql);
    		$st->execute($binds2);
    		$count = $st->fetchColumn();
    		$result['count'] = $count;
    	}catch(Exception $e){
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    		$result['count'] = 0;
    	}		
		return $result;
    }
	/**
     * 获取走访列表--下载
     * @param number $nextNum
     * @param number $pagesize
     * @param unknown $condition
     * @return unknown|Ambigous <number, unknown>
     */
    public static function visitList_down($nextNum = 0,$pagesize = 0,$condition = array()) {
    	$filds_a = "a.`customer_info_id`,a.`customer_name`,a.`province`,a.`city`,a.`area`,a.`street`,a.`general`,a.`ability`,a.`status`,a.`part`,a.`sale_name`";
		$filds_b = "b.`collection_name`,b.`collection_part`,b.`collection_tel`,b.`collection_study`,b.`collection_channel`,b.`collection_date`,b.`agreement`,b.`product`,b.`grade`,b.`format`,b.`number`,b.`betch`,b.`occur_date`,b.`person_name`,b.`person_study`,b.`person_tel`";
		$filds_c = "c.`small_class`,c.`heigh_class`,c.`nature`,c.`main_unit`,c.`relation_unit`,c.`correct_date`,c.`repeat_num`,c.`correction_batch`,c.`correction_date`,c.`solution_unit`,c.`solution_main`,c.`solution_name`,c.`solution_tel`,c.`determi_unit`,c.`question_resean`,c.`is_solution`,c.`solution_measure`,c.`solution_date`,c.`solution_result`,c.`check_num`,c.`solution_status`,c.`repeat_content`,c.`repeat_date`,c.`repeat_name`";
		$filds_d = "d.`question`,d.`question_desc`,a.`author`,a.`editer`";
		$table = "weixin_customer_info a left join weixin_question_info b on a.customer_info_id = b.customer_info_id left join  weixin_question_solution c on b.customer_info_id = c.customer_info_id left join weixin_customer_question d on c.customer_info_id = d.customer_info_id and d.delete_flag = 0";
		$where = "a.customer_info_id = b.customer_info_id  and a.delete_flag =0";
    	$sql = "select {$filds_a},{$filds_b},{$filds_c},{$filds_d} from {$table} where {$where}";
		$where = "";
    	if(is_array($condition)&& count($condition)>0){
    		if(isset($condition['customer_name'])) {
    			$where .= " and a.`customer_name` like '%{$condition['customer_name']}%'";
    		}
			if(isset($condition['sale_name'])) {
    			$where .= " and a.`sale_name` like '%{$condition['sale_name']}%'";
    		}
    	}
    	$where .= " order by a.`raw_add_time` desc";
    	$limit = "";
        if($pagesize != 0 ){
    		$limit = " limit {$nextNum},{$pagesize}";
    	}
    	$result = array();
    	try {
    		$st = self::DB()->dbh->prepare($sql.$where.$limit);
    		$st->execute();
    		$result = $st->fetchAll();
    	}catch(Exception $e){
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    		$result = array();
    	}		
		return $result;
    }
	/*
	* @desc 删除售后访问记录
	* @author kevin
	* @param
	* @return
	**/
	public static function deleteVisitInfo(array $id){
		$ids = implode(',', $id);
		$sql = "update `weixin_customer_info` as a,`weixin_question_info` as b, `weixin_customer_question` as c set a.`delete_flag` = 1,b.`delete_flag` = 1,c.`delete_flag` = 1 where a.`customer_info_id` = b.`customer_info_id` and b.`customer_info_id` = c.`customer_info_id` and a.`customer_info_id` in ({$ids})";
		try {
    		$st = self::DB()->dbh->prepare($sql);
    		$st->execute(array());
    		return true;
    	}catch(Exception $e){
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    		return false;
    	}		
	}
	/*
	* @desc 批量写入订单
	* @author kevin wang
	* @param array
	* @return boolean
	*
	*/
	public static function batch_insert_order(array $orders){
		if(empty($orders)){
			return false;
		}
		
		try{
			$sql = "";
			$i = 0;
			foreach($orders as $order){
				$sql .= "insert into `weixin_order` set ";
				foreach($order as $k=>$v){
					$key = self::$_batch_keywords[$k -1];
					$sql .= "`{$key}` = '{$v}',";
				}
				$sql .= 'raw_add_time=NOW();';
				$i++;
				if($i > 99){
					$sqls[] = $sql;
					$sql = "";
					$i = 0;
				}
				
			}
			if(!empty($sql)){
				$sqls[] = $sql;
			}

			$pdo = self::DB() -> dbh;

			foreach($sqls as $sqlv){
				
				$stm = $pdo -> prepare($sqlv);
    			$stm -> execute();
			}
			
			return true;
    	}catch(Exception $e){
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    		return false;
    	}
	}
	/*
	* @desc 查询问题模块
	* @author kevin wang
	* @param $id int
	* @return array()
	*/
	public static function getVisitHandleById($id){
		$filed = "`customer_info_id`,`small_class`,`heigh_class`,`nature`,`main_unit`,`relation_unit`,`repeat_num`,`correct_date`,`correction_batch`,`correction_date`,`solution_unit`,`solution_main`,`solution_name`,`solution_tel`,`determi_unit`,`question_resean`,`is_solution`,`solution_measure`,`solution_date`,`solution_result`,`check_num`,`solution_status`,`repeat_content`,`repeat_date`,`repeat_name`";
		$sql = "select {$filed} from `weixin_question_solution` where `customer_info_id` = :customer_info_id";
		try {
    		$st = self::DB()->dbh->prepare($sql);
    		$st->execute(array(':customer_info_id' => $id));
    		return $st->fetch();
    	}catch(Exception $e){
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    		return false;
    	}	
	}
	/*
	* @desc 问题模块写入数据
	* @author kevin wang
	* @param $id int
	* @param $data array()
	* @return array()
	*/
	public static function insert_handle($id, $data){
		if(empty($id) || empty($data)){
			return false;
		}
		$handle = self::getVisitHandleById($id);
		if(empty($handle)){
			$sql = "insert into `weixin_question_solution` set ";
			$where = ",`customer_info_id` = {$id},`raw_add_time` = NOW()";
		}else{
			$sql = "update `weixin_question_solution` set ";
			$where = " where `customer_info_id` = {$id}";
		}
		$filed = "";
		foreach($data as $key => $value){
			$filed .= "{$key} = '{$value}',";
		}
		$filed = trim($filed, ",");
		try {
    		$st = self::DB()->dbh->prepare($sql.$filed.$where);
    		$st->execute(array());
			return true;
    	}catch(Exception $e){
    		framework_static_function::write_log($e->getMessage(),'error_sql');
    		return false;
    	}
	}
}
